﻿using PersistentObject.Common;
using PersistentObject.Common.Criteria;
using PersistentObject.Common.Schema;
using PersistentObject.Common.Sorts;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PersistentObject.MySql
{
    public class SqlCreatorForMySql : BaseSqlCreator
    {
        public SqlCreatorForMySql(Table mainTable, BaseDialect dialect)
            :base(mainTable, dialect)
        {
        }

        public SqlCreatorForMySql(Type type, BaseDialect dialect)
            : base(type, dialect)
        {
        }

        public override string CreateCountSql(BaseCriteria criteria)
        {
            return base.CreateCountSql(criteria);
        }

        /// <summary>
        /// select objects from database
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="orderBys"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex">0-base</param>
        /// <returns></returns>
        public override string CreateSelectSql(BaseCriteria criteria, OrderBy[] orderBys, int pageSize, int pageIndex)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select").AppendLine();
            var selectColumnLines = GetSelectClauseLines();
            sb.Append(selectColumnLines);
            sb.Append("from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines);

            if (criteria != null)
            {
                BaseCriteriaCreator criteriaCreator = Dialect.GetCriteriaCreator(MainTable);
                var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
                sb.Append("where ").AppendLine();
                sb.Append(criteriaClause).AppendLine();
            }

            if (orderBys != null && orderBys.Count() > 0)
            {
                BaseOrderByCreator orderByCreator = new BaseOrderByCreator(MainTable, this.Dialect);
                sb.Append("order by ").AppendLine();
                for (int i = 0; i < orderBys.Count(); i++)
                {
                    OrderBy orderBy = orderBys[i];
                    var orderByClause = orderByCreator.GetOrderByClause(orderBy);
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append(orderByClause);
                }
                sb.AppendLine();
            }

            var pagingClause = string.Format("LIMIT {0}, {1}", pageIndex * pageSize, pageSize);
            //offset ({0} * {1}) row fetch next {0} rows only", pageSize, pageIndex);
            sb.Append(pagingClause).AppendLine();

            sb.Append(";");
            var sql = sb.ToString();
            return sql;
        }
 
        public override string CreateInsertSql()
        {
            var keyColumnParameterName = Dialect.NamingPattern.GetSaveParameterName(MainTable.KeyColumn);
            var keyColumnAlias = Dialect.NamingPattern.GetColumnAlias(MainTable.KeyColumn);
            var keyColumnName = Dialect.NamingPattern.GetColumnNameInQuote(MainTable.KeyColumn);
            var keyColumnAliasInQuote = Dialect.NamingPattern.GetColumnAliasInQuote(MainTable.KeyColumn);

            StringBuilder sb = new StringBuilder();
            var mainTableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            sb.Append(string.Format("insert into {0}", mainTableNameInQuote));
            sb.Append("(");
            if (!MainTable.KeyColumn.IsAutoGenerated)
            {
                var keyColumnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(MainTable.KeyColumn);
                sb.Append(string.Format("{0}", keyColumnNameInQuote));
                if (MainTable.ColumnList.Count() > 0)
                {
                    sb.Append(",");
                }
            }
            for (int i = 0; i < MainTable.ColumnList.Count(); i++)
            {
                Column column = MainTable.ColumnList[i];
                var columnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(column);
                if (i != 0)
                {
                    sb.Append(",");
                }
                sb.Append(string.Format("{0}", columnNameInQuote));
            }
            sb.Append(")").AppendLine();
            sb.Append("values(");
            if (!MainTable.KeyColumn.IsAutoGenerated)
            {
                sb.Append(keyColumnParameterName).Append(",");
            }
            for (int i = 0; i < MainTable.ColumnList.Count(); i++)
            {
                Column column = MainTable.ColumnList[i];
                var columnParameterName = Dialect.NamingPattern.GetSaveParameterName(column);
                if (i != 0)
                {
                    sb.Append(",");
                }
                sb.Append(columnParameterName);
            }
            sb.Append(");").AppendLine();
            if (MainTable.KeyColumn.IsAutoGenerated)
            {
                //sb.Append(string.Format("select cast(@@identity as signed integer);"));
                sb.Append(string.Format("select @@identity;"));
            }
            else
            {
                //sb.Append(string.Format("select {0} as {1};", keyColumnParameterName, keyColumnAliasInQuote));
            }
            sb.AppendLine();
            var sql = sb.ToString();
            return sql;
        }

        public override string CreateUpdateSql()
        {
            var keyColumnParameterName = Dialect.NamingPattern.GetSaveParameterName(MainTable.KeyColumn);
            var keyColumnAlias = Dialect.NamingPattern.GetColumnAlias(MainTable.KeyColumn);
            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);

            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("update {0} set", tableNameInQuote)).AppendLine();
            for (int i = 0; i < MainTable.ColumnList.Count(); i++)
            {
                if (i != 0)
                {
                    sb.Append(",").AppendLine();
                }
                Column column = MainTable.ColumnList[i];
                var columnParameterName = Dialect.NamingPattern.GetSaveParameterName(column);
                var columnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(column);
                var clause = string.Format("{0} = {1}", columnNameInQuote, columnParameterName);
                sb.Append(clause);
            }
            sb.AppendLine();
            var keyColumnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(MainTable.KeyColumn);
            sb.Append(string.Format("where {0} = {1};", keyColumnNameInQuote, keyColumnParameterName));
            sb.AppendLine();
            var keyColumnAliasInQuote = Dialect.NamingPattern.GetColumnAliasInQuote(MainTable.KeyColumn);
            sb.Append(string.Format("select {0} from {1} where {0} = {2};",
                keyColumnNameInQuote, tableNameInQuote,
                keyColumnParameterName));
            var sql = sb.ToString();
            return sql;
        }

        public override string CreateDeleteSql()
        {
            var keyColumnParameterName = Dialect.NamingPattern.GetSaveParameterName(MainTable.KeyColumn);
            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            var tableAliasInQuote = Dialect.NamingPattern.GetTableAliasInQuote(MainTable);
            var keyColumnNameInQuote = Dialect.NamingPattern.GetColumnNameWithTableAliasInQuote(MainTable.KeyColumn);

            StringBuilder sb = new StringBuilder();
            sb.Append("SET SQL_SAFE_UPDATES = 0;");
            sb.Append(string.Format("delete {0}", tableAliasInQuote)).AppendLine();
            sb.Append("from ").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines).AppendLine();
            //sb.Append(string.Format("from {0} {1}", tableNameInQuote, tableAliasInQuote)).AppendLine();
            sb.Append(string.Format("where {0} = {1}", keyColumnNameInQuote, keyColumnParameterName)).AppendLine();
            var sql = sb.ToString();
            return sql;
        }

        public override string CreateDeleteSql(BaseCriteria criteria)
        {
            if (criteria == null)
            {
                return CreateDeleteSql();
            }

            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            var tableAliasInQuote = Dialect.NamingPattern.GetTableAliasInQuote(MainTable);

            StringBuilder sb = new StringBuilder();
            sb.Append("SET SQL_SAFE_UPDATES = 0;");
            sb.Append(string.Format("delete {0}", tableAliasInQuote)).AppendLine();
            sb.Append("from ").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines).AppendLine();
            //sb.Append(string.Format("from {0} {1}", tableNameInQuote, tableAliasInQuote)).AppendLine();

            BaseCriteriaCreator criteriaCreator = new BaseCriteriaCreator(MainTable, this.Dialect);
            var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
            sb.Append("where ").AppendLine();
            sb.Append(criteriaClause).AppendLine();

            var sql = sb.ToString();
            return sql;
        }
    }
}
